﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.Globalization;

namespace SQLite2CELib
{
    internal class SelectClauseConverter
    {
        private string _linqSelectClause = "select new {{{0}}}";
        private string _linqSelectClauseColumn = "{0} = each{0},"; //0 = table name
        private string _linqComputedColumn = "computedColumn{0} = {1},"; //0 = index of computed column 1 = computed column expression.
        private string _linqLiteralColumn = "literalColumn{0} = {1},";

        //0 = field name
        private string _linqGroupingClauseForCountFunc = " {0} = grouping.Count() ";

        /*
         *example string freightSum = grouping.Sum(p => p.eachOrder.Freight)
         *0 = field name to which value would be assigned, 1 = Agg function name, 2 = class field name
        */
        private string _linqAggregateFunctionClause = "{0} = grouping.{1}(p => {2})";
        private StringBuilder _selectClause;

        private TableSchemaHelper _schemaHelper;
        private Utils _utils;
        private Dictionary<String, PropertyDetails> _fieldNameAndTypeInfo;
        private int _computedColumCount;
        private int _literalColumnCount;

        public SelectClauseConverter(TableSchemaHelper schemaHelper,
                                     Utils utils)
        {
            _schemaHelper = schemaHelper;
            _utils = utils;
        }

        public StringBuilder Convert(IList<TSqlFragment> selectClauseFragements,
                                     bool hasGroupByClause,
                                     Dictionary<String, List<PropertyDetails>> groupingFieldsSpecs,
                                     List<String> tablesInQuery,
                                     ref Dictionary<String, PropertyDetails> fieldNameAndTypeInfo)
        {
            /* Overall approach
             * CASE 1 - Query has group by clause
             * If Query has group by clause Then simply embed "grouping.Key, grouping" clause inside the select new {  }
             * Then look for Expressions of type function call and append LINQ Mapping function in format '{FunctionName}{ColumnName}(p => p.each{TableName}.{columnname}'
             * CASE 2 - Query does not have group by clause
             * Then iterate over each fragment in list and build expresssion using format 'each{TableName}.{ColumnName}'
             */
            SelectColumn columnInSelect;
            StringBuilder linqExpForColumnsInSelect, linqExpForComputedColumn;
            FunctionCall aggregateFunc;
            Column column;
            KeyValuePair<String, PropertyDetails> validMapping;
            List<KeyValuePair<String, PropertyDetails>> classNameAndTypeInfoForColumnInSelectExp;
            object literalVal;

            _fieldNameAndTypeInfo = fieldNameAndTypeInfo;
            _selectClause = new StringBuilder();

            linqExpForColumnsInSelect = new StringBuilder();
            if (hasGroupByClause == true)
            {
                //Append Grouping and key by default.
                AppendGroupingFieldSpecsToSelectClause(groupingFieldsSpecs, ref linqExpForColumnsInSelect);
                //linqExpForColumnsInSelect = new StringBuilder(" grouping, grouping.Key,  ");
            }

            foreach (TSqlFragment expressionsInSelectClause in selectClauseFragements)
            {
                if (expressionsInSelectClause is SelectColumn == false)
                {
                    continue;
                }
                columnInSelect = expressionsInSelectClause as SelectColumn;

                if (columnInSelect.Expression is Column && hasGroupByClause == false)
                {
                    column = columnInSelect.Expression as Column;
                    switch (column.ColumnType)
                    {
                        case ColumnType.Regular:
                            ProcessRegularColumn(column, ref linqExpForColumnsInSelect);
                            break;
                        case ColumnType.Wildcard:
                            ProcessWildCardColumn(column, tablesInQuery, ref linqExpForColumnsInSelect);
                            break;
                        default:
                            break;
                    }

                }
                //Tool currently does not support queries having agg functions in select clause without group by clause.
                //Example SELECT SUM(OrderDetails.Discount) FROM OrderDetails.
                else if (columnInSelect.Expression is FunctionCall && hasGroupByClause == false)
                {
                    throw new NotSupportedException("Tool currently does not support queries having agg functions in select clause without group by clause.");
                }
                else if (columnInSelect.Expression is FunctionCall && hasGroupByClause == true)
                {
                    aggregateFunc = columnInSelect.Expression as FunctionCall;

                    linqExpForColumnsInSelect.Append(ExtractSelectClauseFromFuncCall(aggregateFunc));
                    linqExpForColumnsInSelect.Append(",");
                }
                else if (columnInSelect.Expression is BinaryExpression || columnInSelect.Expression is ParenthesisExpression)
                {
                    linqExpForComputedColumn = new StringBuilder();
                    classNameAndTypeInfoForColumnInSelectExp = new List<KeyValuePair<string, PropertyDetails>>();

                    ExtractSelectExpression(columnInSelect.Expression, ref linqExpForComputedColumn, ref classNameAndTypeInfoForColumnInSelectExp, true);

                    validMapping = GetFirstValidItem(classNameAndTypeInfoForColumnInSelectExp);
                    linqExpForColumnsInSelect.AppendFormat(_linqComputedColumn, _computedColumCount, linqExpForComputedColumn);
                    _fieldNameAndTypeInfo.Add("computedColumn" + _computedColumCount.ToString(CultureInfo.InvariantCulture), validMapping.Value);
                    _computedColumCount++;
                }
                else if (columnInSelect.Expression is Literal)
                {
                    literalVal = _utils.ExtractValFromLiteral(columnInSelect.Expression as Literal, null);
                    linqExpForColumnsInSelect.AppendFormat(_linqLiteralColumn, _literalColumnCount, literalVal);
                    _fieldNameAndTypeInfo.Add("literalColumn" + _literalColumnCount.ToString(CultureInfo.InvariantCulture),
                                               new PropertyDetails() { TypeInfo = typeof(object) });
                    _literalColumnCount++;
                }
            }

            //Remove extra comma.
            linqExpForColumnsInSelect.Remove(linqExpForColumnsInSelect.Length - 1, 1);
            _selectClause.AppendFormat(_linqSelectClause, linqExpForColumnsInSelect);

            return _selectClause;
        }

        private void ExtractSelectExpression(Expression selectClauseExpression,
                                             ref StringBuilder selectClause,
                                             ref List<KeyValuePair<String, PropertyDetails>> matchingClassNameAndTypeInfoList,
                                             bool appendDoubleCastToColumn)
        {
            string columnExp;
            KeyValuePair<string, PropertyDetails> matchingClassNameAndTypeInfo;
            BinaryExpression selectClauseBinaryExpression = null;
            ParenthesisExpression selectClauseParenthesisExpression = null;

            if (selectClauseExpression is ParenthesisExpression)
            {
                selectClauseParenthesisExpression = selectClauseExpression as ParenthesisExpression;
                selectClause.Append("(");
                ExtractSelectExpression(selectClauseParenthesisExpression.Expression, ref selectClause, ref matchingClassNameAndTypeInfoList, appendDoubleCastToColumn);
                selectClause.Append(")");
                return;
            }

            if (selectClauseExpression is Column)
            {
                columnExp = _utils.ExtractValFromColumn(selectClauseExpression as Column, out matchingClassNameAndTypeInfo);
                if (appendDoubleCastToColumn == true && IsDecimal(matchingClassNameAndTypeInfo.Value.TypeInfo) == true)
                {
                    selectClause.Append("(double)" + columnExp);
                    matchingClassNameAndTypeInfoList.Add(new KeyValuePair<string, PropertyDetails>(matchingClassNameAndTypeInfo.Key,
                                                                                                    new PropertyDetails()
                                                                                                    {
                                                                                                        Name = matchingClassNameAndTypeInfo.Value.Name,
                                                                                                        TypeInfo = typeof(double)
                                                                                                    }));
                }
                else
                {
                    selectClause.Append(columnExp);
                    matchingClassNameAndTypeInfoList.Add(matchingClassNameAndTypeInfo);
                }
                return;
            }

            if (selectClauseExpression is BinaryExpression)
            {
                selectClauseBinaryExpression = selectClauseExpression as BinaryExpression;
                ExtractSelectExpression(selectClauseBinaryExpression.FirstExpression, ref selectClause, ref matchingClassNameAndTypeInfoList, true);
                selectClause.Append(" " + OperatorMappings.GetMapping(selectClauseBinaryExpression.BinaryExpressionType) + " ");
                ExtractSelectExpression(selectClauseBinaryExpression.SecondExpression, ref selectClause, ref matchingClassNameAndTypeInfoList, true);
            }

            if (selectClauseExpression is Literal)
            {
                selectClause.Append(" " + _utils.ExtractValFromLiteral(selectClauseExpression as Literal, null));
            }
        }

        //Method to process select clause with * --> Possible forms *, TableName.*
        private void ProcessWildCardColumn(Column column, List<String> tablesInQuery, ref StringBuilder linqExpForColumnsInSelect)
        {
            Type typeForTable;
            List<string> tableNameInQuery;

            tableNameInQuery = new List<string>();
            //Form --> TableName.*
            if (column.Identifiers.Count == 1)
            {
                tableNameInQuery.Add(column.Identifiers[0].Value);
            }
            //Query has form like SELECT * FORM Customers, Orders..Hence we need to add each table i.e customers and orders
            else
            {
                tableNameInQuery.AddRange(tablesInQuery);
            }

            foreach (String tableName in tableNameInQuery)
            {
                typeForTable = _schemaHelper.GetTypeInfoByName(tableName);
                linqExpForColumnsInSelect.AppendFormat(_linqSelectClauseColumn, tableName);
                _fieldNameAndTypeInfo.Add(tableName, new PropertyDetails() { Name = typeForTable.Name, TypeInfo = typeForTable });
            }
        }

        private void ProcessRegularColumn(Column regularColumn, ref StringBuilder linqExpForColumnInSelect)
        {
            KeyValuePair<String, PropertyDetails> matchingClassNameAndTypeInfo;
            String columnValue;
            String lhsExpForLinqSelect;

            columnValue = _utils.ExtractValFromColumn(regularColumn, out matchingClassNameAndTypeInfo);

            //If we already have a field with current name then append class name as well..
            //For example in query like SELECT Customers.CustomerID, Orders.CustomerID FROM Customers, Orders we have 2 columns in select clause
            //with similar name hence we need to append table name as well.
            lhsExpForLinqSelect = _fieldNameAndTypeInfo.ContainsKey(matchingClassNameAndTypeInfo.Value.Name) ?
                matchingClassNameAndTypeInfo.Key + matchingClassNameAndTypeInfo.Value.Name : matchingClassNameAndTypeInfo.Value.Name;
            linqExpForColumnInSelect.Append("  " + lhsExpForLinqSelect + " = " + columnValue);
            linqExpForColumnInSelect.Append(",");

            //Update Dic that contains the name of properties and their types.
            _fieldNameAndTypeInfo.Add(lhsExpForLinqSelect, matchingClassNameAndTypeInfo.Value);
        }

        private String ExtractSelectClauseFromFuncCall(FunctionCall functionCallExpression)
        {
            String linqFunName;
            String expressionWithLambdaVar;
            String lhsForAggExp; //lhs would be Agg function Name + class name + field name
            StringBuilder expString;
            List<KeyValuePair<String, PropertyDetails>> matchingClassNameAndTypeInfoList;

            expString = new StringBuilder();
            matchingClassNameAndTypeInfoList = new List<KeyValuePair<String, PropertyDetails>>();
            ExtractSelectExpression(functionCallExpression.Parameters[0], ref expString, ref matchingClassNameAndTypeInfoList, false);
            expressionWithLambdaVar = AppendLambdaExpressionVarToColumns(expString, matchingClassNameAndTypeInfoList);

            //For count simply return.. string like count{ColumnName} = grouping.Count()
            if (functionCallExpression.FunctionName.Value.ToUpper(CultureInfo.InvariantCulture) == "COUNT")
            {
                lhsForAggExp = String.Concat("Count_ComputedColumn", _computedColumCount++);
                _fieldNameAndTypeInfo.Add(lhsForAggExp, new PropertyDetails() { TypeInfo = typeof(int) });//Count function returns int..hence hardcoding  
                return String.Format(CultureInfo.InvariantCulture, _linqGroupingClauseForCountFunc, lhsForAggExp);
            }

            //"{0} = grouping.{1}(p => p.{2})"
            linqFunName = OperatorMappings.GetAggregateFunctionMapping(functionCallExpression.FunctionName.Value);
            lhsForAggExp = String.Concat(linqFunName, "_ComputedColumn", _computedColumCount++);
            //There might be multiple columns inside the expression written in computed column..hence I pick first one..!
            _fieldNameAndTypeInfo.Add(lhsForAggExp, matchingClassNameAndTypeInfoList[0].Value);
            return String.Format(CultureInfo.InvariantCulture, _linqAggregateFunctionClause, lhsForAggExp, linqFunName, expressionWithLambdaVar);
        }

        private void AppendGroupingFieldSpecsToSelectClause(Dictionary<string, List<PropertyDetails>> groupingFieldsSpecs, ref StringBuilder linqExpForColumnsInSelect)
        {
            String fieldName;
            foreach (KeyValuePair<String, List<PropertyDetails>> groupingSpec in groupingFieldsSpecs)
            {

                foreach (PropertyDetails eachPropertyDetail in groupingSpec.Value)
                {
                    fieldName = String.Format(CultureInfo.InvariantCulture, "GroupingKey{0}", eachPropertyDetail.Name);
                    linqExpForColumnsInSelect.AppendFormat(CultureInfo.InvariantCulture, "{0} = grouping.Key.{1},", fieldName, eachPropertyDetail.Name);
                    _fieldNameAndTypeInfo.Add(fieldName, eachPropertyDetail);
                }
            }
        }

        private KeyValuePair<String, PropertyDetails> GetFirstValidItem(List<KeyValuePair<String, PropertyDetails>> allItems)
        {
            return (from eachItem in allItems
                    where String.IsNullOrWhiteSpace(eachItem.Key) == false
                    select eachItem).FirstOrDefault();
        }

        private String AppendLambdaExpressionVarToColumns(StringBuilder expression, List<KeyValuePair<String, PropertyDetails>> columnsInQuery)
        {
            List<String> processedColumns;
            String transformedQueryExpression;

            transformedQueryExpression = expression.ToString();
            if (columnsInQuery == null || columnsInQuery.Count == 0)
            {
                return transformedQueryExpression;
            }

            processedColumns = new List<string>();
            foreach (KeyValuePair<String, PropertyDetails> eachColumn in columnsInQuery)
            {
                if (processedColumns.Contains(eachColumn.Key) == false)
                {
                    transformedQueryExpression = transformedQueryExpression.Replace("each" + eachColumn.Key, "p.each" + eachColumn.Key);
                    processedColumns.Add(eachColumn.Key);
                }
            }
            return transformedQueryExpression;
        }

        private bool IsDecimal(Type typeInfo)
        {
            return typeInfo == typeof(decimal) || Nullable.GetUnderlyingType(typeInfo) == typeof(decimal);
        }
    }
}
